import pymysql


class DBUtil:
    conn = None
    # 每个里面都有创建和关闭连接,设为私有进行隐藏
    @classmethod
    # classmethod类方法，调用方便
    def __connect_db(cls):
        cls.conn = pymysql.Connect(host='211.103.136.244',port=7061,user='student',password='iHRM_student_2022',
                                   database='ihrm',charset='utf8')
        return cls.conn
    @classmethod
    def __close_db(cls):
        cls.conn.close()

    # 封装查询
    @classmethod
    def query_db(cls,sql):
        result = None
        cursor = None
        try:
            # 创建连接
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql，返回结果集
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as err:
            print('读取数据错误',err)
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_db()
            return result

    # 封装修改添加删除
    @classmethod
    def uid(cls,sql):
        cursor = None
        try:
            # 创建连接
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql，事务提交、回滚
            cursor.execute(sql)
            affected_rows = cls.conn.affected_rows()
            print('影响数据库的行数：', affected_rows)
            cls.conn.commit()
        except Exception as err:
            print('执行数据库出错',err)
            cls.conn.rollback()
        finally:
            cursor.close()
            cls.__close_db()


if __name__ == '__main__':
    print(DBUtil.query_db('select * from t_book;'))
    DBUtil.uid('update t_book set `read` = `read` +1 where id = 35;')